###################################################################################################
## Payson (2021) When Cities Lobby Replication Code
## Chapter Two Appendix: Table A2.2
## Created with R version 4.0.3 (2020-10-10) -- "Bunny-Wunnies Freak Out"
###################################################################################################


## Load packages
library(tidyverse)


## Set working directory
setwd()


###################################################################################################
## Table A2.2 City Lobbying by State
###################################################################################################

dta <- read.csv("city_lobby.csv")

states <- dta %>%
  group_by(state) %>%
  summarize(first = min(year),
            last = max(year),
            num.years  = (last - first) + 1,
            n = length(fips) / num.years,
            lobby = length(which(lobby.cat != "Never")) / num.years,
            prop.lobby = round((lobby / n)*100),
            min.exp = gsub("Inf", "", as.character(min(lobby.amount.pos, na.rm = T))),
            median.exp = as.character(median(lobby.amount.pos, na.rm = T)),
            mean.exp = gsub("NaN", "", as.character(round(mean(lobby.amount.pos, na.rm = T)))),
            max.exp = gsub("-Inf", "", as.character(max(lobby.amount.pos, na.rm = T))))

states[is.na(states)] <- ""


print(state_table <- data.frame(State = states$state,
                          Years  = paste(states$first, "-", states$last),
                          N = states$n,
                          Lobby = states$prop.lobby,
                          Min.Exp = states$min.exp,
                          Med.Exp = states$median.exp,
                          Mean.Exp = states$mean.exp,
                          Max.Exp = states$max.exp))

